﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DoeObjects
{
    public class Title
    {
        public int TitleId { get; set; }
        public string BookTitle { get; set; }
        public string ISBN10 { get; set; }
        public string ISBN13 { get; set; }
        public int EditionNumber { get; set; }
        public int EditionYear { get; set; }
		public int? FirstEditionYear { get; set; }
        public Publisher Publisher { get; set; }

        public const string BASE_SQL = @"
SELECT * 
FROM title
INNER JOIN publisher
ON title.publisherId = publisher.publisherId 
";

        public static List<Title> getByTitle(string searchTerm, bool unique = false)
        {
            string SQL;

            if (!unique)
            {
                if (searchTerm != null)
                    searchTerm = searchTerm.Replace(' ', '%');
                searchTerm = "%" + searchTerm + "%";
                SQL = BASE_SQL + " WHERE LOWER(Title) LIKE @SEARCH";
            }
            else
            {
                SQL = BASE_SQL + " WHERE LOWER(Title)=@SEARCH";
            }
            List<Title> results = new List<Title>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("SEARCH", searchTerm.ToLower());

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentTitle(dar));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;

        }

        public static List<Title> getAll()
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL, connection);

            List<Title> titles = new List<Title>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    titles.Add(getCurrentTitle(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }

        public static List<Title> getByAuthorName(string searchTerm)
        {
            if (searchTerm != null)
                searchTerm = searchTerm.Replace(' ', '%');

            searchTerm = "%" + searchTerm + "%";
            //string SQL = "WITH myCTE AS (SELECT TITLE.Title, TITLE.ISBN, AUTHOR.aid, AUTHOR.FirstName + ' ' + AUTHOR.LastName AS AuthorName FROM TITLE INNER JOIN Title_has_authors ON Title_has_authors.ISBN=TITLE.ISBN INNER JOIN AUTHOR ON AUTHOR.aid=Title_has_authors.aid) SELECT * FROM myCTE WHERE AuthorName LIKE @SEARCH";
            string SQL = BASE_SQL + " INNER JOIN title_has_authors AS tha ON title.titleId = tha.titleId INNER JOIN author ON tha.Aid = author.Aid WHERE LOWER(author.Name) LIKE @searchTerm";
            List<Title> results = new List<Title>();
            SqlConnection con = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, con);
            cmd.Parameters.AddWithValue("@searchTerm", searchTerm.ToLower());

            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    results.Add(getCurrentTitle(dar));
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return results;
        }

		public static List<Title> getTitlesFromAuthor(int aid)
		{
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand command = new SqlCommand(BASE_SQL + " INNER JOIN title_has_authors AS tha ON title.titleId = tha.titleId WHERE aid = @aid", connection);
			command.Parameters.AddWithValue("@aid", aid);

			List<Title> titles = new List<Title>();
			try
			{
				connection.Open();

				SqlDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					titles.Add(getCurrentTitle(reader));
				}
				reader.Close();
			}
			catch (SqlException ex)
			{
				//something bad happened.
				//TODO: log exception
				throw ex;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}
			return titles;
		}

        public static List<Title> getTitlesFromTopic(int topicId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " INNER JOIN title_has_topics AS tht ON title.titleId = tht.titleId WHERE TopicId = @topicId", connection);
            command.Parameters.AddWithValue("@topicId", topicId);

            List<Title> titles = new List<Title>();

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    titles.Add(getCurrentTitle(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }

        internal static Title getCurrentTitle(SqlDataReader reader)
        {
            Title title = null;

            if (reader != null)
            {
                title = new Title();
                title.TitleId = (int)reader["titleId"];
                title.ISBN10 = (reader["ISBN10"] != DBNull.Value) ? reader["ISBN10"] as string : null;
                title.ISBN13 = (reader["ISBN13"] != DBNull.Value) ? reader["ISBN13"] as string : null;
                title.BookTitle = reader["Title"] as string;
                title.EditionNumber = (int)reader["EditionNumber"];
                title.EditionYear = (int)reader["EditionYear"];
				if (reader["FirstEditionYear"] == DBNull.Value)
					title.FirstEditionYear = null;
				else title.FirstEditionYear = (int)reader["FirstEditionYear"];
                title.Publisher = Publisher.getCurrentPublisher(reader);
            }

            return title;
        }


        public static Title getByIsbn10(string isbn10)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE isbn10 = @isbn", connection);
            SqlParameter isbnParm = new SqlParameter("@isbn", SqlDbType.VarChar);
            isbnParm.Value = isbn10;
            command.Parameters.Add(isbnParm);

            Title title = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    title = getCurrentTitle(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return title;
        }

        public static Title getByIsbn13(string isbn13)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE isbn13 = @isbn", connection);
            SqlParameter isbnParm = new SqlParameter("@isbn", SqlDbType.VarChar);
            isbnParm.Value = isbn13;
            command.Parameters.Add(isbnParm);

            Title title = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    title = getCurrentTitle(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return title;
        }

        /// <summary>
        /// returns the title with the given id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Title getById(int id)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE title.titleId = @id", connection);
            command.Parameters.AddWithValue("@id", id);

            Title title = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    title = getCurrentTitle(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return title;
        }

        /// <summary>
        /// returns all Titles which a user ownes
        /// </summary>
        /// <param name="ownerId"></param>
        /// <returns></returns>
        public static List<Title> getByOwner(int ownerId)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            //SqlCommand command = new SqlCommand("SELECT t.* FROM title AS t INNER JOIN loanable AS l ON t.titleId = l.titleId WHERE l.owner = @ownerId GROUP BY t.titleId, t.ISBN, t.Title, t.EditionYear, t.EditionNumber, t.PublisherId", connection);
            //maybe this sql might be better (instead of this groupby shit, but subselects take longer...):
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE title.titleId IN (SELECT titleId FROM loanable WHERE loanableOwner = @ownerId AND Deleted = 0)", connection);

            command.Parameters.AddWithValue("@ownerId", ownerId);

            List<Title> titles = new List<Title>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    titles.Add(getCurrentTitle(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }

        public static List<Title> getByOwnerName(string owner)
        {
            if (owner != null)
            {
                owner = owner.Replace(" ", "%");
                owner = "%" + owner + "%";
            }

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " INNER JOIN loanable as l ON l.TitleId = title.TitleId INNER JOIN [user] as u ON l.LoanableOwner = u.UserId WHERE LOWER(u.FirstName + ' ' + u.LastName) LIKE @owner", connection);

            command.Parameters.AddWithValue("@owner", owner.ToLower());

            List<Title> titles = new List<Title>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    titles.Add(getCurrentTitle(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }

        /// <summary>
        /// adds a new title and returns the created titleId
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public static int add(Title title)
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO [Title] (ISBN10, ISBN13, Title, EditionNumber, EditionYear, FirstEditionYear, PublisherId) OUTPUT INSERTED.titleId VALUES (@ISBN10, @ISBN13, @Title, @EditionNumber, @EditionYear, @FirstEditionYear, @PublisherId)", connection);


            SqlParameter isbn10Param = new SqlParameter("@ISBN10", SqlDbType.NVarChar);
			if (title.ISBN10 != null) isbn10Param.Value = title.ISBN10;
			else isbn10Param.Value = DBNull.Value;
            SqlParameter isbn13Param = new SqlParameter("@ISBN13", SqlDbType.NVarChar);
			if (title.ISBN13 != null) isbn13Param.Value = title.ISBN13;
			else isbn13Param.Value = DBNull.Value;
            SqlParameter titleParam = new SqlParameter("@Title", SqlDbType.NVarChar);
            titleParam.Value = title.BookTitle;

            cmd.Parameters.Add(isbn10Param);
            cmd.Parameters.Add(isbn13Param);
            cmd.Parameters.Add(titleParam);
            cmd.Parameters.AddWithValue("@EditionNumber", title.EditionNumber);
			cmd.Parameters.AddWithValue("@EditionYear", title.EditionYear);
			if (title.FirstEditionYear != null) cmd.Parameters.AddWithValue("@FirstEditionYear", title.FirstEditionYear);
			else cmd.Parameters.AddWithValue("@FirstEditionYear", DBNull.Value);
            cmd.Parameters.AddWithValue("@PublisherId", title.Publisher.PublisherId);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }

        public static List<Title> searchByIsbn10(string isbn)
        {
            isbn = "%" + isbn + "%";
            isbn = isbn.Replace(" ", "%");
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE isbn10 LIKE @isbn", connection);
            SqlParameter isbnParm = new SqlParameter("@isbn", SqlDbType.VarChar);
            isbnParm.Value = isbn;
            command.Parameters.Add(isbnParm);

            List<Title> titles = new List<Title>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Title title = new Title();
                    title = getCurrentTitle(reader);
                    titles.Add(title);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }

        public static List<Title> searchByIsbn13(string isbn)
        {
            isbn = "%" + isbn + "%";
            isbn = isbn.Replace(" ", "%");
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + " WHERE isbn13 LIKE @isbn", connection);
            SqlParameter isbnParm = new SqlParameter("@isbn", SqlDbType.VarChar);
            isbnParm.Value = isbn;
            command.Parameters.Add(isbnParm);

            List<Title> titles = new List<Title>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Title title = new Title();
                    title = getCurrentTitle(reader);
                    titles.Add(title);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }


        /// <summary>
        /// updates a certain title in the database
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public static bool update(Title title)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE [Title] SET ISBN10 = @ISBN10, ISBN13 = @ISBN13, Title = @Title, EditionNumber = @EditionNumber, EditionYear = @EditionYear, FirstEditionYear = @FirstEditionYear, PublisherId = @PublisherId WHERE titleId = @id", connection);

			SqlParameter isbn10Param = new SqlParameter("@ISBN10", SqlDbType.NVarChar);
			if (title.ISBN10 != null) isbn10Param.Value = title.ISBN10;
			else isbn10Param.Value = DBNull.Value;
			SqlParameter isbn13Param = new SqlParameter("@ISBN13", SqlDbType.NVarChar);
			if (title.ISBN13 != null) isbn13Param.Value = title.ISBN13;
			else isbn13Param.Value = DBNull.Value;
            SqlParameter titleParam = new SqlParameter("@Title", SqlDbType.NVarChar);
            titleParam.Value = title.BookTitle;

            cmd.Parameters.Add(isbn10Param);
            cmd.Parameters.Add(isbn13Param);
            cmd.Parameters.Add(titleParam);
            cmd.Parameters.AddWithValue("@id", title.TitleId);
            cmd.Parameters.AddWithValue("@EditionNumber", title.EditionNumber);
			cmd.Parameters.AddWithValue("@EditionYear", title.EditionYear);
			if (title.FirstEditionYear != null) cmd.Parameters.AddWithValue("@FirstEditionYear", title.FirstEditionYear);
			else cmd.Parameters.AddWithValue("@FirstEditionYear", DBNull.Value);
            cmd.Parameters.AddWithValue("@PublisherId", title.Publisher.PublisherId);
            
            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        /// <summary>
        /// saves a title in the database. depending on the TitleId it will be inserted or updated. if a new one is created (insert) the TitleId in the object will be updated
        /// </summary>
        /// <returns></returns>
        public bool save()
        {
            if (this.TitleId == 0)
            {
                this.TitleId = Title.add(this);
                return (this.TitleId != 0);
            }
            else
            {
                return Title.update(this);
            }
        }

        public static bool addAuthor(int aid, int titleId, bool asEditor)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO Title_has_authors (Aid, TitleId, IsEditor) VALUES (@aid, @titleId, @isEditor)", connection);

            cmd.Parameters.AddWithValue("@aid", aid);
            cmd.Parameters.AddWithValue("@titleId", titleId);
            cmd.Parameters.AddWithValue("@isEditor", asEditor);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

		public static bool addTopic(int topicId, int titleId)
		{
			int affectedRows = 0;
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("INSERT INTO Title_has_topics (TopicId, TitleId) VALUES (@topicId, @titleId)", connection);

			cmd.Parameters.AddWithValue("@topicId", topicId);
			cmd.Parameters.AddWithValue("@titleId", titleId);

			try
			{
				connection.Open();
				affectedRows = cmd.ExecuteNonQuery();
			}
			catch (SqlException er)
			{
				//oo
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return (affectedRows > 0);
		}

        public static bool deleteAuthorTitleReference(int aid, int titleId)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("DELETE FROM title_has_authors WHERE aid = @aid AND titleId = @titleId", connection);
            cmd.Parameters.AddWithValue("@aid", aid);
            cmd.Parameters.AddWithValue("@titleId", titleId);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

		public static bool deleteTopicTitleReference(int topicId, int titleId)
		{
			int affectedRows = 0;
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("DELETE FROM title_has_topics WHERE TopicId = @topicId AND titleId = @titleId", connection);
			cmd.Parameters.AddWithValue("@topicId", topicId);
			cmd.Parameters.AddWithValue("@titleId", titleId);

			try
			{
				connection.Open();
				affectedRows = cmd.ExecuteNonQuery();
			}
			catch (SqlException er)
			{
				//oo
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return (affectedRows > 0);
		}

        public static bool deleteAllAuthorTitleReferences(int titleId)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("DELETE FROM title_has_authors WHERE titleId = @titleId", connection);
            cmd.Parameters.AddWithValue("@titleId", titleId);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

		public static bool deleteAllTopicTitleReferences(int titleId)
		{
			int affectedRows = 0;
			SqlConnection connection = new SqlConnection(Settings.ConnectionString);
			SqlCommand cmd = new SqlCommand("DELETE FROM title_has_topics WHERE titleId = @titleId", connection);
			cmd.Parameters.AddWithValue("@titleId", titleId);

			try
			{
				connection.Open();
				affectedRows = cmd.ExecuteNonQuery();
			}
			catch (SqlException er)
			{
				//oo
				throw er;
			}
			finally
			{
				if (connection != null && connection.State != ConnectionState.Closed)
				{
					connection.Close();
				}
			}

			return (affectedRows > 0);
		}

        /// <summary>
        /// check if the title is avalibe for check out. I.E. more Loanables than active loans and reservations
        /// </summary>
        /// <param name="titleId"></param>
        /// <returns></returns>
        public static bool isAvalible(int titleId)
        {
            bool moreLoanablesAsReservations = false;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmdForCountingReservations = new SqlCommand("SELECT COUNT(*) FROM Reservation WHERE titleId = @titleId", connection);
            SqlCommand cmdForCountingAvailableLoanables = new SqlCommand("SELECT COUNT(*) FROM Loanable WHERE titleId = @titleId AND loanable.UnavailableFromOwner = 0 AND loanable.deleted = 0", connection);
            SqlCommand cmdForCountingOpenLoans = new SqlCommand("SELECT COUNT(*) FROM Loan INNER JOIN Loanable ON Loan.LoanableId = Loanable.LoanableId WHERE Loanable.TitleId = @titleId AND ReturnDate IS NULL", connection);

            //SqlCommand cmd = new SqlCommand("SELECT * FROM Loanable JOIN Loan ON Loanable.LoanableId = Loan.LoanableId WHERE titleId = @titleId AND Loan.LoanId is null", connection);
            cmdForCountingReservations.Parameters.AddWithValue("@titleId", titleId);
            cmdForCountingAvailableLoanables.Parameters.AddWithValue("@titleId", titleId);
            cmdForCountingOpenLoans.Parameters.AddWithValue("@titleId", titleId);

            try
            {
                connection.Open();

                int countReservations = (int)cmdForCountingReservations.ExecuteScalar();
                int countLoanables = (int)cmdForCountingAvailableLoanables.ExecuteScalar();
                int countOpenLoans = (int)cmdForCountingOpenLoans.ExecuteScalar();

                moreLoanablesAsReservations = ((countLoanables - countOpenLoans) > countReservations);
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return moreLoanablesAsReservations;

        }

        /// <summary>
        /// checks if there are any reservations for this title
        /// </summary>
        /// <param name="titleId"></param>
        /// <param name="loanRecalled"></param>
        /// <returns></returns>
        public static bool isReserved(int titleId, bool loanRecalled)
        {
            int numberOfReservations = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Reservation WHERE titleId = @titleId AND loanRecalled = @loanRecalled", connection);
            cmd.Parameters.AddWithValue("@titleId", titleId);
            cmd.Parameters.AddWithValue("@loanRecalled", (loanRecalled) ? 1 : 0);

            try
            {
                connection.Open();
                numberOfReservations = (int)cmd.ExecuteScalar();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (numberOfReservations > 0);

        }


        /// <summary>
        /// returns an orderd list of titles which contains the searchTerm in their bookTitle and which are in at least one of the given topics.
        /// The more topics the title is in (from the given topics) the higher is the title ranked (more at the beginning of the list).
        /// </summary>
        /// <param name="titleSearchTerm">The search term for the bookTitle</param>
        /// <param name="topicNames">the topics</param>
        /// <param name="onlyIfInTopic">if set to true it includes only titles which are in at least one of the given topics. otherwise it returns the titles which matches the searchterm too but at the end of the list</param>
        /// <returns></returns>
        public static List<Title> getTitlesRankedFromTopics(string titleSearchTerm, List<Topic> topics, bool onlyIfInTopic)
        {
            string topicIds = string.Join(", ", topics.Select(t => t.TopicId));
            string sql = @"
SELECT title.*, publisher.*, COUNT(*) AS ranking 
FROM title
INNER JOIN publisher
ON title.publisherId = publisher.publisherId
INNER JOIN title_has_topics AS tht
ON title.titleId = tht.titleId
WHERE tht.topicId IN (" + topicIds + @")
AND LOWER(title.Title) LIKE @searchTerm
GROUP BY title.titleId, title.ISBN10, title.Title, Title.EditionNumber, title.EditionYear, title.FirstEditionYear, title.PublisherId, title.ISBN13, Publisher.PublisherId, Publisher.PublisherName
";
            if (onlyIfInTopic)
            {
                sql += "ORDER BY ranking DESC";
            }
            else
            {
                sql += @"
                        UNION

                        SELECT title.*, publisher.*, '0' AS ranking 
                        FROM title
                        INNER JOIN publisher
                        ON title.publisherId = publisher.publisherId
                        INNER JOIN title_has_topics AS tht
                        ON title.titleId = tht.titleId
                        WHERE tht.topicId NOT IN (" + topicIds + @")
                        AND LOWER(title.Title) LIKE @searchTerm
                        GROUP BY title.titleId, title.ISBN10, title.Title, Title.EditionNumber, title.EditionYear, title.FirstEditionYear, title.PublisherId, title.ISBN13, Publisher.PublisherId, Publisher.PublisherName

                        ORDER BY ranking DESC
                        ";
            }
            

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(sql, connection);

            command.Parameters.AddWithValue("@searchTerm", "%" + titleSearchTerm.ToLower() + "%");

            List<Title> titles = new List<Title>();
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    titles.Add(getCurrentTitle(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return titles;
        }

    }
}
